package com.xu.blog_after_end.dao;

import com.xu.blog_after_end.po.Blog;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;

import java.util.List;
import java.util.Map;

public interface StatisticsRepository extends JpaRepository<Blog, Long> {
    
    // 按月份统计博客发布数量
    @Query(value = "SELECT DATE_FORMAT(create_time, '%Y-%m') as month, COUNT(*) as count FROM t_blog GROUP BY month ORDER BY month", nativeQuery = true)
    List<Map<String, Object>> countBlogByMonth();
    
    // 统计各分类下的博客数量
    @Query(value = "SELECT t.name as typeName, COUNT(b.id) as count FROM t_blog b JOIN t_type t ON b.type_id = t.id GROUP BY t.id ORDER BY count DESC", nativeQuery = true)
    List<Map<String, Object>> countBlogByType();
    
    // 统计各标签下的博客数量
    @Query(value = "SELECT t.name as tagName, COUNT(DISTINCT b.id) as count FROM t_blog b JOIN blog_tag bt ON b.id = bt.blog_id JOIN t_tag t ON bt.tag_id = t.id GROUP BY t.id ORDER BY count DESC", nativeQuery = true)
    List<Map<String, Object>> countBlogByTag();
    
    // 统计访问量最高的博客
    @Query(value = "SELECT id, title, views FROM t_blog ORDER BY views DESC LIMIT 10", nativeQuery = true)
    List<Map<String, Object>> findTopViewedBlogs();
    
    // 统计评论最多的博客
    @Query(value = "SELECT b.id, b.title, COUNT(c.id) as commentCount FROM t_blog b LEFT JOIN t_comment c ON b.id = c.blog_id GROUP BY b.id ORDER BY commentCount DESC LIMIT 10", nativeQuery = true)
    List<Map<String, Object>> findMostCommentedBlogs();
}